<?php

namespace app\common\utils;

use think\facade\Db;

class MysqlUtils
{
    /**
     * 获取数据表可以更新的字段
     * @param string $table_name 数据表名
     * @return array
     */
    public static function getTableUpdateField(string $table_name): array
    {
        $table_field = self::getTableField($table_name);
        unset($table_field['id']);
        unset($table_field['is_deleted']);
        unset($table_field['add_time']);
        unset($table_field['update_time']);

        return array_keys($table_field);
    }

    /**
     * 获取数据表的字段信息
     * @param string $table_name 数据表名
     * @return array
     */
    public static function getTableField(string $table_name): array
    {
        $sql = "show create table {$table_name}";
        $res = Db::query($sql);
        if (empty($res[0]['Create Table'])) {
            return [];
        }

        $table = explode("\n", $res[0]['Create Table']);
        $result = [];
        foreach ($table as $k => $v) {
            if (substr(trim($v), 0, 1) != '`') {
                continue;
            }

            $item = explode('`', $v);

            //字段名称
            $key = $item[1];

            //字段类型:int/varchar/datetime
            $data_type = explode(' ', trim($item[2]))[0];

            //字段注释
            if (strstr($v, 'COMMENT')) {
                $item = explode("'", $v);
                $name = $item[count($item) - 2];
            } else {
                $name = $key;
            }

            //根据 $data_type 设定默认值
            if (strstr($data_type, 'int')) {
                $default_value = 0;
            } elseif (strstr($data_type, 'datetime')) {
                $default_value = echoNowDate();
            } else {
                $default_value = $name;
            }

            //返回数据
            $result[$key] = [
                'name' => $name,
                'data_type' => $data_type,
                'default_value' => $default_value,
            ];
        }

        return $result;
    }

    /**
     * 查询指定数据表的最新一条数据
     * @param string $table_name 数据表名
     * @param string $where where条件
     * @return array
     */
    public static function getDataInfo(string $table_name, string $where): array
    {
        $sql = "select * from {$table_name} where {$where} order by id desc limit 1";
        $data_info = Db::query($sql);
        return $data_info ? $data_info[0] : [];
    }

    /**
     * 封装where条件
     * @param array $where_rule 自定义的条件规则
     * @param array $params 请求参数
     * @return array
     */
    public static function getWhere(array $where_rule, array $params): array
    {
        if (empty($where_rule) || empty($params)) {
            return [];
        }

        $where = [];
        foreach ($where_rule as $field => $val) {
            $type = $val[0];
            $value1 = $params[$val[1][0]] ?? null;
            if (empty($type) || empty($value1)) {
                continue;
            }

            if ($type == 'like') {
                $where[] = [$field, 'like', '%' . $value1 . '%'];
            } elseif ($type == 'between') {
                $value2 = $params[$val[1][1]] ?? null;
                if ($value2) {
                    $where[] = [$field, 'between', [$value1, $value2]];
                }
            } else {
                $where[] = [$field, $type, $value1];
            }
        }

        return $where;
    }

    /**
     * 返回更新数据前后对比结果
     * @param $old_data array 更新前的数据集
     * @param $new_data array 更新后的数据集
     * @return array[]
     */
    public static function getUpdateDataDiff(array $old_data, array $new_data): array
    {
        if (empty($old_data) || empty($new_data)) {
            return [$old_data, $new_data];
        }
        $after_data = array_diff_assoc($new_data, $old_data);
        if (empty($after_data)) {
            return [[], []];
        }
        $after_diff_keys = array_keys($after_data);
        $before_data = [];
        $before_diff_all = array_diff_assoc($old_data, $new_data);
        foreach ($before_diff_all as $k => $v) {
            if (in_array($k, $after_diff_keys)) {
                $before_data[$k] = $v;
            }
        }

        return [$before_data, $after_data];
    }
}